package com.mingqi.enetconnected.data.bean;

import com.j256.ormlite.android.apptools.OpenHelperManager;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.dao.RawRowMapper;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.mingqi.enetconnected.base.MyApplication;
import com.mingqi.enetconnected.data.source.local.DBHelper;

import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.Callable;

public class TestDAO {

    public static void add(){
        try{
            OrmLiteSqliteOpenHelper helper = OpenHelperManager.getHelper(MyApplication.sContext,DBHelper.class);
            Dao<TestDTO,String> dao = helper.getDao(TestDTO.class);

            TestDTO dto = new TestDTO();
            dto.setUserAccount("aaa");
            dto.setUserPwd("bbbb");
//            新增
            dao.create(dto);
//            不存在时新增
            dao.createIfNotExists(dto);
//            新增或更新
            dao.createOrUpdate(dto);

        }catch (Exception e){
            e.printStackTrace();
        }finally{
            OpenHelperManager.releaseHelper();
        }
    }

    public static void query(){
        try{
            OrmLiteSqliteOpenHelper helper = OpenHelperManager.getHelper(MyApplication.sContext,DBHelper.class);
            Dao<TestDTO,String> dao = helper.getDao(TestDTO.class);
            List<TestDTO> res;
//            查所有
            res = dao.queryForAll();
//            查匹配
            res = dao.queryForEq("userAccount","aaa");
//            根据主键查
            TestDTO dto = dao.queryForId("aaa");

//            自定义条件
            QueryBuilder<TestDTO,String> qb = dao.queryBuilder();
            qb.orderBy("createDate",true).distinct().where()
                    .eq("userAccount","aaa");
            res = dao.query(qb.prepare());

//          原生SQL查询,返回数组列表
            String sql = "select userAccount,userPwd from TestTable where TestTable=?";
            GenericRawResults<String []> grr = dao.queryRaw(sql,"aaa");
            List<String[]> arrList = grr.getResults();
//          原生SQL查询,自行封装
            GenericRawResults<TestDTO> grrT = dao.queryRaw(sql, new RawRowMapper<TestDTO>() {
                @Override
                public TestDTO mapRow(String[] columnNames, String[] resultColumns) throws SQLException {
                    TestDTO dto = new TestDTO();
                    dto.setUserAccount(resultColumns[0]);
                    dto.setUserPwd(resultColumns[1]);
                    return dto;
                }
            }, "aaa");
            res = grrT.getResults();



        }catch (Exception e){
            e.printStackTrace();
        }finally{
            OpenHelperManager.releaseHelper();
        }
    }

    private static void delete(){
        try{
            OrmLiteSqliteOpenHelper helper = OpenHelperManager.getHelper(MyApplication.sContext,DBHelper.class);
            Dao<TestDTO,String> dao = helper.getDao(TestDTO.class);

            DeleteBuilder<TestDTO,String> del = dao.deleteBuilder();
            del.where().eq("userAccount","aaa");
            dao.delete(del.prepare());

        }catch (Exception e){
            e.printStackTrace();
        }finally{
            OpenHelperManager.releaseHelper();
        }
    }

    private void update(){
        try{
            OrmLiteSqliteOpenHelper helper = OpenHelperManager.getHelper(MyApplication.sContext,DBHelper.class);
            Dao<TestDTO,String> dao = helper.getDao(TestDTO.class);

            TestDTO dto = dao.queryForId("aaa");
//更新主键
            dao.updateId(dto,"ooo");
//更新非主键
            dto.setUserPwd("zzz");
            dao.update(dto);
//自定义更新
            UpdateBuilder<TestDTO,String> ub = dao.updateBuilder();
            ub.updateColumnValue("userPwd","ddd").where()
                    .eq("userPwd","bbb");


        }catch (Exception e){
            e.printStackTrace();
        }finally{
            OpenHelperManager.releaseHelper();
        }
    }

    /**
     * 批处理
     * 注意批处理不可嵌套
     *
     * */
    public static void batch(){
        try{
            OrmLiteSqliteOpenHelper helper = OpenHelperManager.getHelper(MyApplication.sContext,DBHelper.class);
            final Dao<TestDTO,String> dao = helper.getDao(TestDTO.class);
//方法一
            dao.callBatchTasks(new Callable<Object>() {
                @Override
                public Object call() throws SQLException {
                    for(int i=0;i<10;i++){
                        dao.create(new TestDTO());
                    }
                    return null;
                }
            });


        }catch (Exception e){
            e.printStackTrace();
        }finally{
            OpenHelperManager.releaseHelper();
        }
    }
}
